<?php
namespace sql;

use PDOException;

/**
 * 用法：
 * 1.指定列
 * SqlTable::build()->table("file")
 * ->columns("f_id,f_name")
 * ->where(SqlWhere::build()->eq("f_id=1"))
 * ->reads($obj);
 * 
 * 2.批量读取所有列
 * SqlTable::build()->table("file")
 * ->where(SqlWhere::build()->eq("f_id=1"))
 * ->reads($obj);
 * 
 * 1.通过DataBaseAttribute
 * SqlTable::build()->table("file")
 * ->dataAttr(attr)
 * ->where(SqlWhere::build()->eq("f_id=1"))
 * ->reads();
 * 
 * 插入数据：
 * SqlTable::build()->table("file")->insert($obj);
 * 
 * 删除：
 * SqlTable::build()->table("file")
 * ->where(SqlWhere::build()->eq())
 * ->delete();
 * 
 * 测试：
 * SqlTable::build()->table("file")
 * ->dataAttr(attr)
 * ->where(SqlWhere::build()->eq("f_id=1"))
 * ->reads();
 */
class SqlTable{
    private $m_table;//数据表名称
    private $m_columns="";
    private $m_where = null;

    public static function build()
    {
        return new SqlTable();
    }

    function __construct()
    {
    }

    function table($t)
    {
        $this->m_table=$t;
        return $this;
    }

    function columns($cols)
    {
        $this->m_columns=$cols;
        return $this;
    }

    function where($w)
    {
        $this->m_where = $w;
        return $this;
    }

    function dataAttr(&$dba/**DataBaseAttribute */)
    {
        $this->m_columns = $dba->getColumnNames();
        return $this;
    }

    function insert(&$cls)
    {
        $sql = SqlBuilder::build()->table($this->m_table)
        ->dataAttr($cls)
        ->insert();

        $stmt = SqlCommand::build()->sql($sql)->execStmt($sql);
        $cls->bindStmt($stmt);
        $stmt->execute();

    }

    function read(&$cls/** DataBaseAttribute */)
    {
        $sql = SqlBuilder::build()->table($this->m_table)
        ->columns($this->m_columns)
        ->where($this->m_where)
        ->select();

        $st = SqlCommand::build()->sql($sql)->execStmt();
        $this->m_where->bind($st);
        $st->execute();
        $row = $st->fetch();
        $cols = $cls->getColumns();

        $ref = new \ReflectionClass($cls);
        foreach($cols as $n => $field)
        {
            $pro = $ref->getProperty($n);
            $pro->setValue($cls,$row[$field->name]);
        }
    }

    function reads($cls)
    {
        //未指定列=>选择所有列
        if(empty($this->m_columns))
            $this->m_columns = $cls->getColumnNames();

        $rets = array();
        $sql = SqlBuilder::build()->table($this->m_table)
        ->columns($this->m_columns)
        ->where($this->m_where)
        ->select();

        $st = SqlCommand::build()->sql($sql)->execStmt();
        $this->m_where->bind($st);
        $st->execute();
        $rows = $st->fetchAll();
        $cols = $cls->getColumns();
        $ref = new \ReflectionClass($cls);
        
        foreach($rows as $row)
        {
            $obj = $ref->newInstance();
            $rc = new \ReflectionClass($obj);
            foreach($cols as $n => $field/** SqlField */)
            {
                $pro = $rc->getProperty($n);
                $pro->setValue($obj,$field->pdoValue($row[$field->name]));
            }
            $rets[]=$obj;
        }
        return $rets;
    }

    function clear()
    {
        SqlCommand::build()->sql("truncate table ".$this->m_table)
        ->exec();
    }

    /**
     * $cls
     * $cols 更新指定的列 id,name
     */
    function update(&$cls,$cols="")
    {
        $sql = SqlBuilder::build()->table($this->m_table)
        ->dataAttr($cls)
        ->update($cols);
    }

    function delete()
    {
        if(is_null($this->m_where))
            $this->m_where = SqlWhere::build();

        $sql = SqlBuilder::build()->table($this->m_table)
        ->where($this->m_where)
        ->delete();

        SqlCommand::build()->sql($sql)->exec();
    }
}
?>